
Sys.createController('Sqlite', {

	dbPath: Sys.DATA_PATH + '/test.db',
	
	getConnection: function() {
		return new ConnectionFactory('sqlite').getConnection(this.dbPath);
	},
	
	indexAction: function() {
	
		var data = this.query(Params.name, Params.occupation);
		this.render('index', {title: 'SQLite test', dbPath: this.dbPath, data: data});
	},
	
	initdbAction: function() {
	
		var conn = this.getConnection();
		var stat = conn.createStatement();
		
		stat.executeUpdate('drop table if exists people;');
		stat.executeUpdate('create table people (id integer NOT NULL PRIMARY KEY AUTOINCREMENT, name, occupation);');
	
		var rows = [
			{name: 'Gandhi', occupation: 'politics'},
			{name: 'Turing', occupation: 'computers'},
			{name: 'Wittgenstein', occupation: 'smartypants'}
		];
		
		this.insertRow(rows);
		conn.close();
		this.redirect('sqlite/index');
	},
	
	insertRow: function(rows) {
		
		rows = isArray(rows) ? rows : [];
		var conn = this.getConnection();
		var prep = null;
		try {
			prep = conn.prepareStatement('insert into people (name, occupation) values (?, ?);');
		} catch(e) {
			print(e);
			return;
		}

		for (var i=0,l=rows.length; i<l; i++) {
			
			var row = rows[i];
			prep.setString(1, row.name);
			prep.setString(2, row.occupation);
			prep.addBatch();
		}
		
		conn.setAutoCommit(false);
		try {
			prep.executeBatch();
		} catch(e) {
			print(e);
		}
		conn.setAutoCommit(true);

		conn.close();
	},
	
	insertAction: function() {
		
		var data = [
			{name: Params.name, occupation: Params.occupation}
		];
		
		this.insertRow(data);
		this.redirect('sqlite/index');
	},
	
	removeRow: function(rows) {
		
		rows = isArray(rows) ? rows : [];
		var conn = this.getConnection();
		var prep = null;
		try {
			prep = conn.prepareStatement('delete from people where id = ?;');
		} catch(e) {
			print(e);
			return;
		}
		
		for (var i=0,l=rows.length; i<l; i++) {
			
			var row = rows[i];
			prep.setString(1, row.id);
			prep.addBatch();
		}
		
		conn.setAutoCommit(false);
		try {
			prep.executeBatch();
		} catch(e) {
			print(e);
		}
		conn.setAutoCommit(true);

		conn.close();
	},
	
	removeAction: function() {
		
		var data = [
			{id: Params.id}
		];
		
		this.removeRow(data);
		this.redirect('sqlite/index');
	},
	
	query: function(name, occupation) {
		
		var where = [];
		if (!isEmpty(name)) {
			where.push(printf("name = '%s'",  name));
		}
		if (!isEmpty(occupation)) {
			where.push(printf("occupation = '%s'",  occupation));
		}
		
		var data = [];
		var conn = this.getConnection();
		var stat = conn.createStatement();
		var rs = null;
		
		try {
			
			var sql = 'select * from people';
			if (where.length > 0) {
				sql += ' where ' + where.join(' and ');
			}
			
			rs = stat.executeQuery(sql);
			// NOTE: Trimpath doesn't provide a while loop
			while (rs.next()) {
				data.push({id: rs.getString('id'), name: rs.getString('name'), occupation: rs.getString('occupation')});
			}
			rs.close();

		} catch (e) {
			print(e);
		}
		
		conn.close();
		return data;
	}
	
});
